package com.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author wt
 * @desc Excel工具类
 * @date: 2018-7-6 13:46:26
 */
public class ExcelUtil {

    /**
     * 单例
     */
    private ExcelUtil() {}

    private static class SingletonInstance {
        private static final ExcelUtil INSTANCE = new ExcelUtil();
    }

    public static ExcelUtil getInstance() {
        return SingletonInstance.INSTANCE;
    }


    // key：sheet名称 value：sheet数据
    private Map<String, List<String[]>> sheetData;

    /**
     * 创建Excel文件
     * @param sheetData
     * @return
     * @throws Exception
     */
    public Workbook createExcel(Map<String, List<String[]>> sheetData) throws Exception {

        // 创建表格
        Workbook workbook = new HSSFWorkbook();

        // 循环数据创建sheet
        sheetData.forEach((name, data) -> {
            // 创建sheet
            Sheet sheet = workbook.createSheet(name);
            // 写入数据
            for (int i = 0; i < data.size(); i++) {
                String[] rowData = data.get(i);
                // 创建行
                Row row = sheet.createRow(i);
                for (int j = 0; j < rowData.length; j++) {
                    // 创建表格
                    Cell cell = row.createCell(j);
                    cell.setCellValue(rowData[j]);
                }
            }
        });

        return workbook;
    }

    /**
     * 上传解析
     * @param multipartFile
     * @return
     * @throws Exception
     */
    public Map<String,List<List<String>>> importData(MultipartFile multipartFile) throws Exception {
        InputStream is = null;
        if (multipartFile != null && !multipartFile.isEmpty()) {
            is = multipartFile.getInputStream();
        }

        Workbook workbook = WorkbookFactory.create(is);

        return getSheet(workbook,1);
    }


    /**
     * 读取文件
     * @param is
     * @return
     * @throws Exception
     */
    public Map<String,List<List<String>>> readExcel(InputStream is) throws Exception {

        Workbook workbook = WorkbookFactory.create(is);

        return getSheet(workbook,0);
    }

    /**
     * 获取sheet数据
     * @param workbook
     * @return
     */
    private Map<String,List<List<String>>> getSheet(Workbook workbook,int startRowIndex) {

        Map<String,List<List<String>>> result = new LinkedHashMap<>();

        //获取每个Sheet表
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            // 获取一个Sheet表
            Sheet sheet = workbook.getSheetAt(i);
            // 获取sheet名称
            String sheetName = sheet.getSheetName();
            // 行数据
            List<List<String>> data = new LinkedList<>();
            for (int j = startRowIndex; j < sheet.getLastRowNum() + 1; j++) {
                // 获取行数据
                Row row = sheet.getRow(j);
                if(row == null){
                    continue;
                }
                List<String> cellList = new LinkedList<>();
                for (int k = 0; k < row.getLastCellNum(); k++) {
                    Cell cell = row.getCell(k);
                    String cellData = coverCell(cell);
                    cellList.add(cellData);
                }
                data.add(cellList);
            }
            result.put(sheetName,data);
        }

        return result;
    }

    /**
     * 转换cell中的数据
     *
     * @param cell 单元格
     */
    private String coverCell(Cell cell) {
        String result = "";

        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellTypeEnum().name()) {
                case "NUMERIC": // 数字
                    DecimalFormat df = new DecimalFormat("0");
                    result = df.format(cell.getNumericCellValue());
                    break;
                case "STRING": // 字符串
                    result = cell.getStringCellValue();
                    break;
                case "FORMULA":
                    result = cell.getCellFormula();
                    break;
                default:
                    result = "";
                    break;
            }
        }
        return result;
    }



}
